package com.yuda.afterexamscore.controller;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.yuda.afterexamscore.service.ISpartaSubjectService;
import com.yuda.afterexamscore.vo.DpQuesScoreVo;
import io.swagger.annotations.Api;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 阿杜需求，附加科目小题分数据分析
 *
 * @author fmx
 * @date 2021年7月9日
 */
@RestController
@RequestMapping("/additionalSubjectsAnalysis")
@Api("阿杜需求：导出附加科目小题分数据分析excel表")
@RefreshScope
public class AdditionalSubjectsAnalysisByADuController {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Value("${examUUID}")
    private String examUUID;

    @Autowired
    private ISpartaSubjectService spartaSubjectService;

    private static Logger log = LoggerFactory.getLogger(AdditionalSubjectsAnalysisByADuController.class);

//    private static Queue<DpQuesScoreVo> questions = new ConcurrentLinkedQueue<>();

//    private static List<DpQuesScoreVo> questionsList = new CopyOnWriteArrayList<>();

    @GetMapping
    public void getExcel(HttpServletRequest request, HttpServletResponse response) throws InterruptedException {
        ExcelWriter writer = ExcelUtil.getWriter("e:/加试科目小题分析.xlsx","加试一");
        //查看是否有部分对
        List<DpQuesScoreVo> sectionRight = jdbcTemplate.query("SELECT ques_id,count(1) c from th_ques_score_mapping where subject_id = 1013 and score > 0 GROUP BY ques_id HAVING c > 1", new BeanPropertyRowMapper<>(DpQuesScoreVo.class));
        //有部分对情况
        if (CollectionUtil.isNotEmpty(sectionRight)){
            creatSheetSectionRight(writer,1013l);
        }else {
            creatSheet(writer,1013l);
        }
        //查看是否有部分对
        List<DpQuesScoreVo> sectionRight2 = jdbcTemplate.query("SELECT ques_id,count(1) c from th_ques_score_mapping where subject_id = 1014 and score > 0 GROUP BY ques_id HAVING c > 1", new BeanPropertyRowMapper<>(DpQuesScoreVo.class));
        //有部分对情况
        if (CollectionUtil.isNotEmpty(sectionRight2)){
            creatSheetSectionRight(writer,1014l);
        }else {
            creatSheet(writer,1014l);
        }
        writer.close();
        System.out.println("All right!!!");
    }

    public void creatSheet(ExcelWriter writer,Long subjectId){


        List<DpQuesScoreVo> questionsList = jdbcTemplate.query("SELECT subject_relation_id,question_id quesId,ques_index from dp_ques_index_mapping \n" +
                "where subject_relation_id = ? order by ques_index+0",new Object[]{subjectId} , new BeanPropertyRowMapper<>(DpQuesScoreVo.class));
        setValue(questionsList);
        DpQuesScoreVo voOne = new DpQuesScoreVo();
        List<Double> distinctionList = questionsList.stream().filter(q -> q.getSubjectRelationId().equals(subjectId)).map(q -> q.getDistinction()).collect(Collectors.toList());
        double distinctionAvg = distinctionList.stream().mapToDouble((x) -> x).average().getAsDouble();
        voOne.setDistinctionAvg(distinctionAvg);

        List<Double> difficultyList = questionsList.stream().filter(q -> q.getSubjectRelationId().equals(subjectId)).map(q -> q.getDifficulty()).collect(Collectors.toList());
        double difficultyAvg = difficultyList.stream().mapToDouble((x) -> x).average().getAsDouble();
        voOne.setDifficultyAvg(difficultyAvg);
        Integer count = jdbcTemplate.queryForObject("select count(1) c from dp_ques_index_mapping where subject_id = ?", new Object[]{subjectId}, Integer.class);
        Double reliabilityOne = jdbcTemplate.queryForObject("SELECT "+count+"/"+(count-1)+"*(1-SUM(a.canshu)) FROM\n" +
                "(SELECT (STD(a.score)*STD(a.score))/(STD(b.score)*STD(b.score)) canshu\n" +
                "FROM dp_ques_score a\n" +
                "LEFT JOIN dp_subject_score b ON a.zkz_num = b.zkz_num and a.subject_id = b.subject_id\n" +
                "WHERE a.subject_id= ? \n" +
                "GROUP BY a.ques_index) a",new Object[]{subjectId} ,Double.class);
        voOne.setReliability(Double.parseDouble(NumberUtil.round(reliabilityOne,2)+""));
        if (subjectId.equals(1014l)){
            writer.setSheet("加试二");
        }
        List<Object> row1 = new ArrayList<>();
        row1.add("");
        row1.add("题号");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row1.add(vo.getQuesIndex());
            }
        }
        //高分段
        List<Object> row2 = new ArrayList<>();
        row2.add("高分组");
        row2.add("答对");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row2.add(vo.getHightRight());
            }
        }
        List<Object> row3 = new ArrayList<>();
        row3.add("");
        row3.add("答错");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row3.add(vo.getHightErr());
            }
        }
        List<Object> row4 = new ArrayList<>();
        row4.add("");
        row4.add("难度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row4.add(vo.getHightDifficulty());
            }
        }
        List<Object> row5 = new ArrayList<>();
        row5.add("");

        //中分段
        List<Object> row6 = new ArrayList<>();
        row6.add("中分组");
        row6.add("答对");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row6.add(vo.getMidRight());
            }
        }
        List<Object> row7 = new ArrayList<>();
        row7.add("");
        row7.add("答错");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row7.add(vo.getMidErr());
            }
        }
        List<Object> row8 = new ArrayList<>();
        row8.add("");
        row8.add("难度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row8.add(vo.getMidDifficulty());
            }
        }
        List<Object> row9 = new ArrayList<>();
        row9.add("");

        //低分段
        List<Object> row10 = new ArrayList<>();
        row10.add("低分组");
        row10.add("答对");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row10.add(vo.getLowRight());
            }
        }
        List<Object> row11 = new ArrayList<>();
        row11.add("");
        row11.add("答错");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row11.add(vo.getLowErr());
            }
        }
        List<Object> row12 = new ArrayList<>();
        row12.add("");
        row12.add("难度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row12.add(vo.getLowDifficulty());
            }
        }
        List<Object> row13 = new ArrayList<>();
        row13.add("");

        //区分度等
        List<Object> row14 = new ArrayList<>();
        row14.add("");
        row14.add("区分度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row14.add(vo.getDistinction());
            }
        }

        List<Object> row15 = new ArrayList<>();
        row15.add("");
        row15.add("平均区分度/效度");
        row15.add(voOne.getDistinctionAvg());

        List<Object> row16 = new ArrayList<>();
        row16.add("");
        row16.add("难度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row16.add(vo.getDifficulty());
            }
        }

        List<Object> row17 = new ArrayList<>();
        row17.add("");
        row17.add("平均难度");
        row17.add(voOne.getDifficultyAvg());

        List<Object> row18 = new ArrayList<>();
        row18.add("");
        row18.add("信度");
        row18.add(voOne.getReliability());

        List<Object> row19 = new ArrayList<>();
        row19.add("");
        row19.add("信度等级");
        if (voOne.getReliability()<0.5){
            row19.add("较差");
        }else if (voOne.getReliability()>0.8){
            row19.add("非常好");
        }else {
            row19.add("较好");
        }
        List<List<Object>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5, row6, row7,row8, row9, row10, row11, row12, row13, row14,row15, row16, row17, row18, row19);
        writer.merge(1,3,0,0,null,true);
        writer.merge(5,7,0,0,null,true);
        writer.merge(9,11,0,0,null,true);
        writer.write(rows);
    }

    public void creatSheetSectionRight(ExcelWriter writer,Long subjectId){
        List<DpQuesScoreVo> questionsList = jdbcTemplate.query("SELECT subject_relation_id,question_id quesId,ques_index from dp_ques_index_mapping \n" +
                "where subject_relation_id = ? order by ques_index+0",new Object[]{subjectId} , new BeanPropertyRowMapper<>(DpQuesScoreVo.class));
        setValueSectionRight(questionsList);
        DpQuesScoreVo voOne = new DpQuesScoreVo();
        List<Double> distinctionList = questionsList.stream().filter(q -> q.getSubjectRelationId().equals(subjectId)).map(q -> q.getDistinction()).collect(Collectors.toList());
        double distinctionAvg = distinctionList.stream().mapToDouble((x) -> x).average().getAsDouble();
        voOne.setDistinctionAvg(distinctionAvg);

        List<Double> difficultyList = questionsList.stream().filter(q -> q.getSubjectRelationId().equals(subjectId)).map(q -> q.getDifficulty()).collect(Collectors.toList());
        double difficultyAvg = difficultyList.stream().mapToDouble((x) -> x).average().getAsDouble();
        voOne.setDifficultyAvg(difficultyAvg);
        //获取科目题数
        Integer count = jdbcTemplate.queryForObject("select count(1) c from dp_ques_index_mapping where subject_id = ?", new Object[]{subjectId}, Integer.class);
        Double reliabilityOne = jdbcTemplate.queryForObject("SELECT "+count+"/"+(count-1)+"*(1-SUM(a.canshu)) FROM\n" +
                "(SELECT (STD(a.score)*STD(a.score))/(STD(b.score)*STD(b.score)) canshu\n" +
                "FROM dp_ques_score a\n" +
                "LEFT JOIN dp_subject_score b ON a.zkz_num = b.zkz_num and a.subject_id = b.subject_id\n" +
                "WHERE a.subject_id= ? \n" +
                "GROUP BY a.ques_index) a",new Object[]{subjectId} ,Double.class);
        voOne.setReliability(Double.parseDouble(NumberUtil.round(reliabilityOne,2)+""));
        if (subjectId.equals(1014l)){
            writer.setSheet("加试二");
        }
        List<Object> row1 = new ArrayList<>();
        row1.add("");
        row1.add("题号");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row1.add(vo.getQuesIndex());
            }
        }
        //高分段
        List<Object> row2 = new ArrayList<>();
        row2.add("高分组");
        row2.add("答对");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row2.add(vo.getHightRight());
            }
        }
        //高分段
        List<Object> row2_5 = new ArrayList<>();
        row2_5.add("");
        row2_5.add("部分对");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row2_5.add(vo.getHightSectionRight());
            }
        }
        List<Object> row3 = new ArrayList<>();
        row3.add("");
        row3.add("答错");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row3.add(vo.getHightErr());
            }
        }
        List<Object> row4 = new ArrayList<>();
        row4.add("");
        row4.add("难度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row4.add(vo.getHightDifficulty());
            }
        }
        List<Object> row5 = new ArrayList<>();
        row5.add("");

        //中分段
        List<Object> row6 = new ArrayList<>();
        row6.add("中分组");
        row6.add("答对");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row6.add(vo.getMidRight());
            }
        }
        List<Object> row6_5 = new ArrayList<>();
        row6_5.add("");
        row6_5.add("部分对");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row6_5.add(vo.getMidSectionRight());
            }
        }
        List<Object> row7 = new ArrayList<>();
        row7.add("");
        row7.add("答错");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row7.add(vo.getMidErr());
            }
        }
        List<Object> row8 = new ArrayList<>();
        row8.add("");
        row8.add("难度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row8.add(vo.getMidDifficulty());
            }
        }
        List<Object> row9 = new ArrayList<>();
        row9.add("");

        //低分段
        List<Object> row10 = new ArrayList<>();
        row10.add("低分组");
        row10.add("答对");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row10.add(vo.getLowRight());
            }
        }
        List<Object> row10_5 = new ArrayList<>();
        row10_5.add("");
        row10_5.add("部分对");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row10_5.add(vo.getLowSectionRight());
            }
        }
        List<Object> row11 = new ArrayList<>();
        row11.add("");
        row11.add("答错");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row11.add(vo.getLowErr());
            }
        }
        List<Object> row12 = new ArrayList<>();
        row12.add("");
        row12.add("难度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row12.add(vo.getLowDifficulty());
            }
        }
        List<Object> row13 = new ArrayList<>();
        row13.add("");

        //区分度等
        List<Object> row14 = new ArrayList<>();
        row14.add("");
        row14.add("区分度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row14.add(vo.getDistinction());
            }
        }

        List<Object> row15 = new ArrayList<>();
        row15.add("");
        row15.add("平均区分度/效度");
        row15.add(voOne.getDistinctionAvg());

        List<Object> row16 = new ArrayList<>();
        row16.add("");
        row16.add("难度");
        for (DpQuesScoreVo vo : questionsList) {
            if (vo.getSubjectRelationId().equals(subjectId)){
                row16.add(vo.getDifficulty());
            }
        }

        List<Object> row17 = new ArrayList<>();
        row17.add("");
        row17.add("平均难度");
        row17.add(voOne.getDifficultyAvg());

        List<Object> row18 = new ArrayList<>();
        row18.add("");
        row18.add("信度");
        row18.add(voOne.getReliability());

        List<Object> row19 = new ArrayList<>();
        row19.add("");
        row19.add("信度等级");
        if (voOne.getReliability()<0.5){
            row19.add("较差");
        }else if (voOne.getReliability()>0.8){
            row19.add("非常好");
        }else {
            row19.add("较好");
        }
        List<List<Object>> rows = CollUtil.newArrayList(row1, row2,row2_5, row3, row4, row5, row6,row6_5, row7,row8, row9, row10, row10_5,row11, row12, row13, row14,row15, row16, row17, row18, row19);
        writer.merge(1,4,0,0,null,true);
        writer.merge(6,9,0,0,null,true);
        writer.merge(11,14,0,0,null,true);
        writer.write(rows);
    }

    public void setValue(List<DpQuesScoreVo> list){
        for (DpQuesScoreVo vo : list) {
            Object[] objects = new Object[]{vo.getQuesId()};
            Integer hightRight = jdbcTemplate.queryForObject("SELECT count(1) c from dp_subject_score a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "where b.ques_id = ?  and a.percentage_g >73 and b.score+0 > 0",objects, Integer.class);
            vo.setHightRight(hightRight);
            Integer hightErr = jdbcTemplate.queryForObject("SELECT count(1) c from dp_subject_score a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "where b.ques_id = ?  and a.percentage_g >73 and b.score+0 = 0",objects, Integer.class);
            vo.setHightErr(hightErr);
            vo.setHightDifficulty(NumberUtil.div((double)hightRight,(double)(hightRight+hightErr)));
            Integer midRight = jdbcTemplate.queryForObject("SELECT count(1) c from dp_subject_score a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "where b.ques_id = ?  and a.percentage_g <=73 and a.percentage_g >27 and b.score+0 > 0",objects, Integer.class);
            vo.setMidRight(midRight);
            Integer midErr = jdbcTemplate.queryForObject("SELECT count(1) c from dp_subject_score a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "where b.ques_id = ?  and a.percentage_g <=73 and a.percentage_g >27 and b.score+0 = 0",objects, Integer.class);
            vo.setMidErr(midErr);
            vo.setMidDifficulty(NumberUtil.div((double)midRight,(double)(midRight+midErr)));
            Integer lowRight = jdbcTemplate.queryForObject("SELECT count(1) c from dp_subject_score a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "where b.ques_id = ?  and a.percentage_g <=27 and b.score+0 > 0",objects, Integer.class);
            vo.setLowRight(lowRight);
            Integer lowErr = jdbcTemplate.queryForObject("SELECT count(1) c from dp_subject_score a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "where b.ques_id = ?  and a.percentage_g <=27 and b.score+0 = 0",objects, Integer.class);
            vo.setLowErr(lowErr);
            vo.setLowDifficulty(NumberUtil.div((double)lowRight,(double)(lowRight+lowErr)));
            //计算区分度
            vo.setDistinction( vo.getHightDifficulty() -  vo.getLowDifficulty() );
            //计算难度
            Double difficulty = jdbcTemplate.queryForObject("select AVG(a.score)/b.full_score from dp_ques_score a\n" +
                    "LEFT JOIN dp_ques_index_mapping b on a.ques_id = b.question_id\n" +
                    "where a.ques_id = ?",objects, Double.class);
            vo.setDifficulty(Double.parseDouble(NumberUtil.round(difficulty,2)+""));
        }
    }

    public void setValueSectionRight(List<DpQuesScoreVo> list){
        //查科姆
        Long subjectRelationId = list.get(0).getSubjectRelationId();
        //查找一共多少人
        Long sum = jdbcTemplate.queryForObject("select count(1) c from dp_subject_score where subject_id = ?", new Object[]{list.get(0).getSubjectRelationId()}, Long.class);
        Long hight = (long)(sum * 0.27);
        Long low = (long)(sum * 0.73);
        for (DpQuesScoreVo vo : list) {
            Object[] objects = new Object[]{subjectRelationId,vo.getQuesId()};
            //高
            Integer hightRight = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "LEFT JOIN dp_ques_index_mapping c on b.ques_id = c.question_id\n" +
                    "where b.ques_id = ?  and a.row_num <="+hight+" and b.score+0 = c.full_score",objects, Integer.class);
            vo.setHightRight(hightRight);
            Integer hightSectionRight = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "LEFT JOIN dp_ques_index_mapping c on b.ques_id = c.question_id\n" +
                    "where b.ques_id = ?  and a.row_num <="+hight+" and b.score+0 > 0 and b.score+0 < c.full_score",objects, Integer.class);
            vo.setHightSectionRight(hightSectionRight);
            Integer hightErr = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "where b.ques_id = ?  and a.row_num <="+hight+" and b.score+0 = 0",objects, Integer.class);
            vo.setHightErr(hightErr);
            Double hightDifficulty = jdbcTemplate.queryForObject("SELECT AVG(a.score)/c.full_score avgdiff from dp_ques_score a\n" +
                    "left JOIN (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "LEFT JOIN dp_ques_index_mapping c on a.ques_id = c.question_id\n" +
                    "where a.ques_id = ?  and b.row_num <="+hight,objects, Double.class);
            vo.setHightDifficulty(Double.parseDouble(NumberUtil.round(hightDifficulty,2)+""));
            //中
            Integer midRight = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "LEFT JOIN dp_ques_index_mapping c on b.ques_id = c.question_id\n" +
                    "where b.ques_id = ?  and a.row_num >"+hight+" and a.row_num <"+low+" and b.score+0 = c.full_score",objects, Integer.class);
            vo.setMidRight(midRight);
            Integer midSectionRight = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "LEFT JOIN dp_ques_index_mapping c on b.ques_id = c.question_id\n" +
                    "where b.ques_id = ?  and a.row_num >"+hight+" and a.row_num <"+low+"  and b.score+0 > 0 and b.score+0 < c.full_score",objects, Integer.class);
            vo.setMidSectionRight(midSectionRight);
            Integer midErr = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "where b.ques_id = ?  and a.row_num >"+hight+" and a.row_num <"+low+"  and b.score+0 = 0",objects, Integer.class);
            vo.setMidErr(midErr);
            Double midDifficulty = jdbcTemplate.queryForObject("SELECT AVG(a.score)/c.full_score avgdiff from dp_ques_score a\n" +
                    "left JOIN (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "LEFT JOIN dp_ques_index_mapping c on a.ques_id = c.question_id\n" +
                    "where a.ques_id = ?  and b.row_num >"+hight+" and b.row_num <"+low+" ",objects, Double.class);
            vo.setMidDifficulty(Double.parseDouble(NumberUtil.round(midDifficulty,2)+""));
            //低
            Integer lowRight = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "LEFT JOIN dp_ques_index_mapping c on b.ques_id = c.question_id\n" +
                    "where b.ques_id = ?  and a.row_num >="+low+" and b.score+0 = c.full_score",objects, Integer.class);
            vo.setLowRight(lowRight);
            Integer lowSectionRight = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "LEFT JOIN dp_ques_index_mapping c on b.ques_id = c.question_id\n" +
                    "where b.ques_id = ?  and a.row_num >="+low+" and b.score+0 > 0 and b.score+0 < c.full_score",objects, Integer.class);
            vo.setLowSectionRight(lowSectionRight);
            Integer lowErr = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) a \n" +
                    "left JOIN dp_ques_score b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "where b.ques_id = ?  and a.row_num >="+low+" and b.score+0 = 0",objects, Integer.class);
            vo.setLowErr(lowErr);
            Double lowDifficulty = jdbcTemplate.queryForObject("SELECT AVG(a.score)/c.full_score avgdiff from dp_ques_score a\n" +
                    "left JOIN (SELECT *,row_number() OVER(order by score+0 desc) as row_num from dp_subject_score where subject_id = ?) b on a.zkz_num = b.zkz_num and a.subject_id = b.subject_id \n" +
                    "LEFT JOIN dp_ques_index_mapping c on a.ques_id = c.question_id\n" +
                    "where a.ques_id = ?  and b.row_num >="+low,objects, Double.class);
            vo.setLowDifficulty(Double.parseDouble(NumberUtil.round(lowDifficulty,2)+""));
            //计算区分度
            vo.setDistinction( vo.getHightDifficulty() -  vo.getLowDifficulty() );
            //计算难度
            Double difficulty = jdbcTemplate.queryForObject("select AVG(score)/b.full_score from dp_ques_score a\n" +
                    "LEFT JOIN dp_ques_index_mapping b on a.ques_id = b.question_id\n" +
                    "where b.subject_id = ? and a.ques_id = ?",objects, Double.class);
            vo.setDifficulty(Double.parseDouble(NumberUtil.round(difficulty,2)+""));
        }
    }
}
